package org.magicsoft.office.excel.impt;

import java.sql.Connection;

import org.magicsoft.office.db.DatabaseType;
import org.magicsoft.office.db.DatabaseUtils;
import org.magicsoft.office.excel.model.Reference;
import org.magicsoft.office.excel.model.Sheet;
import org.magicsoft.office.excel.model.TargetTable;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ForeignKeysUpdater {

	private static final Logger logger = LoggerFactory.getLogger(ForeignKeysUpdater.class);

	public void update(Sheet sheet, Connection conn, DatabaseType databaseType) {
		for (Reference reference : sheet.getReferences()) {
			this.updateForeignKeyFromOriginal(sheet, conn, databaseType, reference);
			if (reference.getRefTableName().equals(reference.getColumn().getBindTable().getTableName())) {
				this.updateParentIdFromTempTable(sheet, conn, databaseType, reference);
			}
		}
	}

	/**
	 * 从原始表更新外键。<br />
	 * 
	 * 示例SQL：<br />
	 * 
	 * <pre>
	 * update #cd_cadrefiletype set cadrefilecategoryid_ = cd_cadrefilecategory.id_ from #cd_cadrefiletype 
	 * left join cd_cadrefilecategory on cd_cadrefilecategory.name0_ = #cd_cadrefiletype.cadrefilecategoryidmapping_
	 * </pre>
	 * 
	 * @param column
	 *            当前列。
	 */
	private void updateForeignKeyFromOriginal(Sheet sheet, Connection conn, DatabaseType databaseType,
			Reference refColumn) {
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append("update ");
		sqlBuilder.append(sheet.getTempTableName());
		sqlBuilder.append(" set (");
		sqlBuilder.append(refColumn.getTempTableColumn());
		sqlBuilder.append(") = (select ");
		sqlBuilder.append(refColumn.getRefTableName());
		sqlBuilder.append(".");
		sqlBuilder.append(refColumn.getRefPrimaryKey().getColumn());
		sqlBuilder.append(" from ");
		sqlBuilder.append(refColumn.getRefTableName());
		sqlBuilder.append(" where ");
		sqlBuilder.append(refColumn.getRefTableName());
		sqlBuilder.append(".");
		sqlBuilder.append(refColumn.getRefDisplayColumn());
		sqlBuilder.append(" = ");
		sqlBuilder.append(sheet.getTempTableName());
		sqlBuilder.append(".");
		sqlBuilder.append(refColumn.getColumn().getTempTableColumn());
		sqlBuilder.append(")");
		String sql = DatabaseUtils.translateJoinUpdate(sqlBuilder.toString(), databaseType);
		logger.debug("update foreignkey from original sql:" + sql);
		DatabaseUtils.executeUpdate(conn, sql);
	}

	/**
	 * 对于自关联属性，从原始表更新之后还需要从临时表再更新一次。 <br />
	 * 
	 * 示例SQL：(cd_area为目标表， code_为更新依据)<br />
	 * update #cd_area set parentid_ = t2.nid__ <br/>
	 * from #cd_area <br/>
	 * left join #cd_area t2 on t2.code_ = #cd_area.parentidmapping_ <br/>
	 * and t2.errormsg__ is null
	 * 
	 * @param column
	 *            引用列。
	 */
	private void updateParentIdFromTempTable(Sheet sheet, Connection conn, DatabaseType databaseType,
			Reference reference) {
		String nidColumn = null;
		for (TargetTable targetTable : sheet.getTargetTables()) {
			if (targetTable.getTableName().equals(reference.getRefTableName())) {
				nidColumn = targetTable.getNidColumn();
				break;
			}
		}

		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append("update ");
		sqlBuilder.append(sheet.getTempTableName());
		sqlBuilder.append(" set (");
		sqlBuilder.append(reference.getTempTableColumn());
		sqlBuilder.append(") = (select t2.");
		sqlBuilder.append(nidColumn);
		sqlBuilder.append(" from ");
		sqlBuilder.append(sheet.getTempTableName());
		sqlBuilder.append(" t2 where t2.");
		sqlBuilder.append(reference.getMappingColumn().getTempTableColumn());
		sqlBuilder.append(" = ");
		sqlBuilder.append(sheet.getTempTableName());
		sqlBuilder.append(".");
		sqlBuilder.append(reference.getColumn().getTempTableColumn());
		sqlBuilder.append(")");
		String sql = DatabaseUtils.translateJoinUpdate(sqlBuilder.toString(), databaseType);
		logger.debug("update foreignkey from temp table sql:" + sql);
		DatabaseUtils.executeUpdate(conn, sql);
	}

}
